package fy.input.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.DBConfigBean;
import fy.util.DBUtil;
import java.util.ArrayList;

class MySQLSetQueryDao {

    private static Log log = LogFactory.getLog(MySQLSetQueryDao.class);
    private String[] args;
    private String result;
    private MySQLSetBean sqlBean;

    private String ERROR_SQL_IS_NULL = "{success : false, msg : '参数错误，请联系OA系统管理员。'}";

    private DBConfigBean dbConfigBean;

    /**
     * DRIVERCLASSNAME, URL, USERNAME, PASSWORD
     *
     * @param dataSetArray
     */
    public void setDataSetArray(DBConfigBean dbConfigBean) {
        this.dbConfigBean = dbConfigBean;
    }

    public String getResult() {
        return result;
    }

    public void setArgs(String[] args) {
        this.args = args;
    }

    public void setSQLBean(MySQLSetBean sqlBean) {
        this.sqlBean = sqlBean;
    }

    public void query() {
        if (sqlBean == null) {
            result = ERROR_SQL_IS_NULL;
        } else {
            log.info(new StringBuilder().append("开始查询，本次查询使用ＳＱＬ语句:").append(
                    sqlBean.getSqlList()));
            Connection conn = null;
            PreparedStatement pst = null;
            ResultSet rs = null;

            try {
                conn = DBUtil.connect(dbConfigBean);
                List<String> strList = sqlBean.getSqlList();
                JSONObject jo = new JSONObject();
                int count = 0;
                for (int k = 0, size = strList.size(); k < size; k++) {
                    pst = conn.prepareStatement(strList.get(k));
                    if (args != null) {
                        for (int i = 0, length = args.length; i < length; i++) {
                            pst.setString(i + 1, args[i]);
                        }
                    }

                    rs = pst.executeQuery();

                    JSONObject child = new JSONObject();
                    while (rs.next()) {
                        JSONObject row = new JSONObject();
                        ResultSetMetaData md = rs.getMetaData();
                        String name = null;
                        for (int i = 0, length = md.getColumnCount(); i < length; i++) {
                            name = md.getColumnName(i + 1);
                            row.put(name, rs.getString(i + 1));
                            name = null;
                        }
                        md = null;
                        name = null;
                        child.append("rows", row);
                        row = null;
                        count++;
                    }
                    jo.append("children", child);
                }
                if (count > 0) {
                    jo.put("success", true);
                    jo.put("fields", sqlBean.getFieldName());
                    result = jo.toString();
                } else {
                    result = getfailResult(sqlBean.getNullMsg(),
                            sqlBean.getFieldName());
                }
                jo = null;
            } catch (Exception e) {
                result = getfailResult(sqlBean.getErrorMsg(),
                        sqlBean.getFieldName());
                log.error(result, e);
                e.printStackTrace();
            } finally {
                DBUtil.close(rs);
                DBUtil.close(pst);
                DBUtil.close(conn);
            }
        }
        sqlBean = null;
    }

    public List<List<String>> query_expExcel() {
        List<List<String>> result = null;
        //行
        List<String> row = null;
        //标题
        List<String> header = null;
        if (sqlBean != null) {
            log.info(new StringBuilder().append("开始查询，本次查询使用ＳＱＬ语句:").append(
                    sqlBean.getSqlList()));
            Connection conn = null;
            PreparedStatement pst = null;
            ResultSet rs = null;
            try {
                conn = DBUtil.connect(dbConfigBean);
                List<String> strList = sqlBean.getSqlList();
                for (int k = 0, size = strList.size(); k < size; k++) {
                    pst = conn.prepareStatement(strList.get(k));
                    if (args != null) {
                        for (int i = 0, length = args.length; i < length; i++) {
                            pst.setString(i + 1, args[i]);
                        }
                    }

                    rs = pst.executeQuery();
                    int columnCount = 0;
                    if (header == null) {
                        header = new ArrayList<String>();
                        columnCount = rs.getMetaData().getColumnCount();
                        for (int m = 0; m < columnCount; m++) {
                            header.add(rs.getMetaData().getColumnLabel(m));
                        }
                        result.add(header);
                    }

                    while (rs.next()) {
                        if (row == null) {
                            row = new ArrayList<String>();
                        }
                        for (int m = 0; m < columnCount; m++) {
                            row.add(rs.getString(m));
                        }
                    }
                    if (result == null) {
                        result = new ArrayList<List<String>>();
                    }
                    result.add(row);
                    row = null;
                }
            } catch (Exception e) {
                log.error("ZZC_自定义导出功能导出Excel发生异常", e);
                e.printStackTrace();
            } finally {
                DBUtil.close(rs);
                DBUtil.close(pst);
                DBUtil.close(conn);
            }
        }
        return result;
    }

    private String getfailResult(String msg, String fieldName) {
        try {
            JSONObject jo = new JSONObject();
            jo.put("success", false);
            jo.put("msg", msg);
            jo.put("fields", fieldName);
            return jo.toString();
        } catch (Exception e) {
            return "{success:false,msg:'系统发生异常，请重试或者联系OA系统管理员。'}";
        }
    }
}
